﻿using Dapper;
using Microsoft.VisualBasic.ApplicationServices;
using System.Data;
using System.Data.SQLite;
using System.Diagnostics;
using System.Linq;
using System.Reflection.Metadata;
using System.Text;
using System.Threading.Tasks;
using System.Xml.Serialization;
using TomatoClock.Models;
using TomatoClock.ViewModels;
using static System.Windows.Forms.LinkLabel;

namespace TomatoClock.Services
{


    public class ProcessManager
    {
        //   private readonly static string filePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "resource", "ForbiddenAppGroups.xml");
        private static readonly string dbPath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory,@"Data\TomatoClock.db");
        private MainViewModel viewModel;//尚没有获得当前userid方法  传入viewModel 实际上替代了user
        private readonly SQLiteConnection _connection;
        public int UserId;
        private string connectionString = $"Data Source={dbPath};Version=3;";

        public ProcessManager()
        {
            _connection = new SQLiteConnection($"Data Source={dbPath};Version=3;");
            UserId = viewModel.CurrentUser.UserId;
            _connection.Open();
        }
        public ProcessManager(MainViewModel viewModel)
        {
            this.viewModel = viewModel;
            UserId=viewModel.CurrentUser.UserId;
        }
        private SQLiteConnection OpenConnection()
        {
            var connection = new SQLiteConnection(connectionString);
            connection.Open();
            return connection;
        }
        public List<GroupProcess> GetProcesses(int userId, int groupId)
        {
            using (var connection = OpenConnection())
            {
                string query = @"
                SELECT Id, ProcessId, GroupId 
                FROM GroupProcesses
                WHERE UserId = @UserId AND GroupId = @GroupId";
                var parameters = new { UserId = userId, GroupId = groupId };
                return connection.Query<GroupProcess>(query, parameters).AsList();
            }
        }

        // BanProcess methods
        public BanGroup LoadGroupProesses()
        {
            var resultGroup = new BanGroup();
            resultGroup = GetBanGroup(UserId);
            resultGroup.Processes=new List<int>();
            var Processes= GetProcesses(UserId, resultGroup.GroupId);
            foreach (var process in Processes)
            {
                resultGroup.Processes.Add(process.ProcessId);
            }
            return resultGroup;
        }
        public List<BanGroup> LoadUserGroups(int userId)
        {
            using (var connection = OpenConnection())
            {
                // 查询 BanGroup 表获取所有 BanGroup 对象
                string banGroupQuery = @"
        SELECT UserId, GroupId, GroupName
        FROM BanGroup
        WHERE UserId = @UserId";

                var banGroups = connection.Query<BanGroup>(banGroupQuery, new { UserId = userId }).ToList();

                if (banGroups.Any())
                {
                    // 获取所有 GroupId
                    var groupIds = banGroups.Select(bg => bg.GroupId).Distinct().ToList();

                    // 查询 GroupProcess 表获取相关 GroupProcess 对象
                    string groupProcessQuery = @"
            SELECT UserId AS userId, ProcessId AS processId, GroupId AS groupId
            FROM GroupProcess
            WHERE GroupId IN @GroupIds";

                    var groupProcesses = connection.Query<GroupProcess>(groupProcessQuery, new { GroupIds = groupIds }).ToList();

                    // 为每个 BanGroup 对象赋值 Processes
                    foreach (var banGroup in banGroups)
                    {
                        banGroup.Processes = groupProcesses
                            .Where(gp => gp.GroupId == banGroup.GroupId)
                            .Select(gp => gp.ProcessId)
                            .ToList();
                    }
                }

                return banGroups;
            }
        }
        public BanProcess GetProcessById(int processId)
        {
            using (var connection = OpenConnection())
            {
                string query = @"
                SELECT ProcessId, ProcessName
                FROM BanProcess
                WHERE ProcessId = @ProcessId";

                var parameters = new { ProcessId = processId };
                return connection.QueryFirstOrDefault<BanProcess>(query, parameters);
            }
        }
        public BanGroup GetBanGroup(int userId)
        {
            using (var connection = OpenConnection())
            {
                string query = @"
                SELECT Id, UserId, GroupId, BanDate
                FROM BanGroup
                WHERE UserId = @UserId
                LIMIT 1";
                var parameters = new { UserId = userId };
                return connection.QueryFirstOrDefault<BanGroup>(query, parameters);
            }
        }
        













        //
        //===========================================================
        //
        public List<string> GetCurrentProcess()
        {
            List<string> currentProcess = new List<string>();
            foreach (var process in Process.GetProcesses())
            {
                currentProcess.Add(process.ProcessName);
            }
            return currentProcess;
        }
        
        public void CreateGroup(string groupName)
        {
            // 使用数据库连接和命令插入新的组名到BanGroup表
            // 需要包含UserId以关联到当前用户
            using (var connection = new SQLiteConnection($"Data Source={dbPath};Version=3;"))
            {
                connection.Open();
                using (var command = new SQLiteCommand(connection))
                {
                    command.CommandText = "INSERT INTO BanGroup (UserId,GroupName) VALUES (@userId,@groupName)";
                    command.Parameters.AddWithValue("@groupName", groupName);
                    command.Parameters.AddWithValue("@userId",UserId);
                    command.ExecuteNonQuery();
                }
            }
        }


        public void DeleteGroup(string groupName)
        {
            using (var connection = new SQLiteConnection($"Data Source={dbPath};Version=3;"))
            {
                connection.Open();

                using (var transaction = connection.BeginTransaction())
                {
                    try
                    {
                        // 获取 GroupId
                        string groupIdQuery = "SELECT GroupId FROM BanGroup WHERE GroupName = @groupName AND UserId = @userId";
                        int groupId = connection.QueryFirstOrDefault<int>(groupIdQuery, new { groupName, userId = UserId });

                        if (groupId > 0)
                        {
                            // 删除 GroupProcess 表中对应的记录
                            string deleteGroupProcessQuery = "DELETE FROM GroupProcess WHERE GroupId = @groupId AND UserId = @userId";
                            connection.Execute(deleteGroupProcessQuery, new { groupId, userId = UserId }, transaction);

                            // 删除 BanGroup 表中的记录
                            string deleteGroupQuery = "DELETE FROM BanGroup WHERE GroupId = @groupId AND UserId = @userId";
                            connection.Execute(deleteGroupQuery, new { groupId, userId = UserId }, transaction);

                            transaction.Commit();
                        }
                        else
                        {
                            throw new Exception("Group not found.");
                        }
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        Console.WriteLine($"Exception occurred: {ex.Message}");
                        throw;
                    }
                }
            }
        }




        public void AddProcess(string processName)
        {
            using (var connection = OpenConnection())
            {
                // 查询是否存在同名的 processName
                string checkQuery = "SELECT COUNT(1) FROM BanProcess WHERE ProcessName = @Name";
                var parameters = new { Name = processName };

                // 执行查询
                int count = connection.ExecuteScalar<int>(checkQuery, parameters);

                // 如果不存在同名的 processName，则插入数据
                if (count == 0)
                {
                    string insertQuery = "INSERT INTO BanProcess (ProcessName) VALUES (@Name)";
                    connection.Execute(insertQuery, parameters);
                }
            }
        }
        public void AddGroupProcess(string groupName, string programName)
        {
            var processId = ProcessChangeToId(programName);
            var groupId = GroupChangeToId(groupName);
            using (var connection = new SQLiteConnection($"Data Source={dbPath};Version=3;"))
            {
                connection.Open();
                using (var command = new SQLiteCommand(connection))
                {
                    command.CommandText = "INSERT INTO GroupProcess VALUES (@userId,@groupId,@processId)";
                    command.Parameters.AddWithValue("@groupId", groupId);
                    command.Parameters.AddWithValue("@userId", UserId);
                    command.Parameters.AddWithValue("@processId", processId);
                    command.ExecuteNonQuery();
                }
            }
        }
        //简单转换
        public string ProcessChangeToString(int processId) {
            using (var connection = OpenConnection())
            {
                string query = @"
                SELECT ProcessName
                FROM BanProcess
                WHERE ProcessId=@ProcessId
                LIMIT 1";
                var parameters = new { ProcessId = processId };
                return connection.QueryFirstOrDefault<string>(query, parameters);
            }
        }
        //简单转换
        public int GroupChangeToId(string groupName)
        {
            using(var connection = OpenConnection())
            {
                string query = @"
                SELECT GroupId
                FROM BanGroup
                WHERE GroupName=@GroupName
                LIMIT 1";
                var parameters = new { GroupName = groupName };
                return connection.QueryFirstOrDefault<int>(query, parameters);
            }                 
        }
        //简单转换
        public int ProcessChangeToId(string processName)
        {
            using (var connection = OpenConnection())
            {
                string query = @"
                SELECT ProcessId
                FROM BanProcess
                WHERE ProcessName=@ProcessName
                LIMIT 1";
                var parameters = new { ProcessName = processName };
                return connection.QueryFirstOrDefault<int>(query, parameters);
            }
        }
        public void DeleteGroupProcess(string groupName, string programName)
        {
            var processId = ProcessChangeToId(programName);
            var groupId = GroupChangeToId(groupName);
            using (var connection = new SQLiteConnection($"Data Source={dbPath};Version=3;"))
            {
                connection.Open();

                string query = "DELETE FROM GroupProcess WHERE GroupId = @GroupId " +
                               "AND UserId = @UserId AND ProcessId = @ProcessId";

                using (var command = new SQLiteCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@GroupId", groupId);
                    command.Parameters.AddWithValue("@UserId", UserId);
                    command.Parameters.AddWithValue("@ProcessId", processId);
                    command.ExecuteNonQuery();
                }
            }
        }
        //     public static void SaveChanges()
        //     {
        // 使用事务保存对数据库的修改
        //    }
        public List<ForbiddenAppGroup> LoadGroups()
        {
            List<ForbiddenAppGroup> forbiddenAppGroups = new List<ForbiddenAppGroup>();

            using (var connection = new SQLiteConnection($"Data Source={dbPath};Version=3;"))
            {
                connection.Open();

                var query = @"
        SELECT bg.GroupName, bp.ProcessName
        FROM BanGroup bg
        JOIN BanProcess bp ON bg.userId = bp.userId AND bg.groupName = bp.groupName
        WHERE bg.userId = @userId";

                var lookup = new Dictionary<string, ForbiddenAppGroup>();

                connection.Query<ForbiddenAppGroup, string, ForbiddenAppGroup>(
                    query,
                    (group, processName) =>
                    {
                        if (!lookup.TryGetValue(group.GroupName, out var foundGroup))
                        {
                            foundGroup = group;
                            foundGroup.ForbiddenApps = new List<string>();
                            lookup.Add(foundGroup.GroupName, foundGroup);
                        }
                        foundGroup.ForbiddenApps.Add(processName);
                        return foundGroup;
                    },
                    new { userId=UserId },
                    splitOn: "ProcessName"
                );

                forbiddenAppGroups = lookup.Values.ToList();
            }

            return forbiddenAppGroups;
        }

        public void SelectBanGroups(string groupNames) //选择禁止进程组
        {
            using (var connection = new SQLiteConnection($"Data Source={dbPath};Version=3;"))
            {
                connection.Open();
                using (var command = new SQLiteCommand(connection))
                {
                    command.CommandText = "DELETE FROM SelectedBanGroup WHERE UserId =@userId ";
                    command.Parameters.AddWithValue("@userId", UserId);
                    command.ExecuteNonQuery();
                }
            }

            using (var connection = new SQLiteConnection($"Data Source={dbPath};Version=3;"))
            {
                connection.Open();
                using (var command = new SQLiteCommand(connection))
                {   
                    foreach(var groupName in groupNames) 
                    {
                        command.CommandText = "INSERT OR REPLACE INTO SelectedBanGroup (UserId, GroupName) VALUES (@userId, @groupName)";
                        command.Parameters.AddWithValue("@userId", UserId);
                        command.Parameters.AddWithValue("@groupName", groupName);
                        command.ExecuteNonQuery();
                    }

                }
            }
        }
        public List<string> GetBanGroups()
        {   
            List<string> groups = new List<string>();
            using (var connection = new SQLiteConnection($"Data Source={dbPath};Version=3;"))
            {
                connection.Open();
                using (var command = new SQLiteCommand(connection))
                {
                    command.CommandText = @"
                  SELECT groupName
                  FROM SelectedBanGroup 
                  WHERE SelectedBanGroup.userId = @userId";
                    command.Parameters.AddWithValue("@userId", UserId);
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            string groupName = reader.GetString(0);
                            groups.Add(groupName);
                        }
                    }
                }
            }
            return groups;
        }
        public List<string> LoadProcesses(string groupName)
        {
            // 将组名转换为组ID
            var groupId = GroupChangeToId(groupName);
            List<string> processes = new List<string>();

            // 使用 SQLite 连接
            using (var connection = new SQLiteConnection($"Data Source={dbPath};Version=3;"))
            {
                connection.Open();
                using (var command = new SQLiteCommand(connection))
                {
                    // 修改查询语句，连接 GroupProcess 和 BanProcess 表，直接获取 ProcessName
                    command.CommandText = @"
                SELECT bp.ProcessName
                FROM GroupProcess gp
                JOIN BanProcess bp ON gp.ProcessId = bp.ProcessId
                WHERE gp.GroupId = @GroupId AND gp.UserId = @userId";

                    // 添加参数
                    command.Parameters.AddWithValue("@GroupId", groupId);
                    command.Parameters.AddWithValue("@userId", UserId);

                    // 执行查询并读取结果
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            string processName = reader.GetString(0);
                            processes.Add(processName);
                        }
                    }
                }
            }

            // 返回进程名称列表
            return processes;
        }

        public List<string> GetSelectedProcess(string groupName)
        {
            List<string> processNames = new List<string>();
            using (SQLiteConnection connection = new SQLiteConnection($"Data Source={dbPath};Version=3;"))
            {       
                connection.Open();
                using (SQLiteCommand command = new SQLiteCommand(connection))
                {
                    command.CommandText = @"
                   SELECT BP.ProcessId
                   FROM SelectedBanGroup SBG
                   INNER JOIN GroupProcess BP ON SBG.UserId = BP.UserId AND SBG.GroupName = @groupName
                   WHERE SBG.UserId = @userId";
                    command.Parameters.AddWithValue("@userId", UserId);
                    command.Parameters.AddWithValue("@groupName",groupName);

                    using (SQLiteDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            int processId = reader.GetInt32(0);
                            string processName = ProcessChangeToString(processId);
                            // 将每个processname添加到列表中
                            processNames.Add(processName);
                        }
                    }
                }
            }
            return processNames;
        }
        public ProcessMonitor MonitorProcesses()
        {
            List<string> selectedProcess=GetSelectedProcess(GetBanGroup(UserId).GroupName);
            ProcessMonitor processMonitor=new ProcessMonitor(selectedProcess);
            return processMonitor;
        }
    }
}


